using System;
using System.Collections.Generic;
using System.Text;
using emp.Model.SysManage;
using emp.IDAL.SysManage;
using emp.Utility;
using emp.SqlServerDAL;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace emp.SqlServerDAL.SysManage
{
    public class Organization :IOrganization
    {
        #region IOrganization Members

        private const string SQL_SELECT_NODE = " select organiz_uid,parent_id,organiz_name,organiz_code,organiz_desc,organiz_type,organiz_sord from organization where organiz_uid = @Organiz_ID and is_del='0'";
        private const string SQL_SELECT_CHILDLIST = " select organiz_uid,parent_id,organiz_name,organiz_desc from organization where parent_id = @Parent_ID and is_del='0'";

        private const string SQL_INSERT = " exec p_save_organiz @Parent_ID,@Organiz_Name,@Organiz_Code,@Organiz_Desc, @Organiz_Type,@Organiz_Sord,@Creator";
        private const string SQL_UPDATE = " exec p_update_organiz @Organiz_ID,@Parent_ID,@Organiz_Name,@Organiz_Code, @Organiz_Desc, @Organiz_Type,@Organiz_Sord";
        private const string SQL_DELETE = " exec p_delete_organiz @Organiz_ID";

        private const string PARM_ORGANIZ_ID = "@Organiz_ID";
        private const string PARM_PARENT_ID = "@Parent_ID";
        private const string PARM_ORGANIZ_NAME = "@Organiz_Name";
        private const string PARM_ORGANIZ_CODE = "@Organiz_Code";
        private const string PARM_ORGANIZ_DESC = "@Organiz_Desc";
        private const string PARM_ORGANIZ_TYPE = "@Organiz_Type";
        private const string PARM_ORGANIZ_SORD = "@Organiz_Sord";
        private const string PARM_CREATOR = "@Creator";
        private const string PARM_ISDEL = "@Is_Del";

        public int Insert(OrganizInfo myOrganizInfo)
        {
            SqlParameter[] Parms = GetInsertParameters();
            SetInsertParameters(Parms, myOrganizInfo);

            int iIsSuccess = 0;

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        iIsSuccess = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT, Parms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.SysManage.Organization.Insert(OrganizInfo myOrganizInfo)";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return iIsSuccess;
        }

        public int Modify(OrganizInfo myOrganizInfo)
        {
            SqlParameter[] itemParms = GetModifyParameters();
            SetModifyParameters(itemParms, myOrganizInfo);
            int iIsSuccess = 0;

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        iIsSuccess = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, itemParms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.SysManage.Organization.Modify(OrganizInfo myOrganizInfo)";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return iIsSuccess;
        }

        public int Delete(int iOrganiz_ID)
        {
            SqlParameter itemParms = new SqlParameter(PARM_ORGANIZ_ID, SqlDbType.Int, 4, "organiz_uid");
            itemParms.Value = iOrganiz_ID;

            int iIsSuccess = 0;

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        iIsSuccess = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_DELETE, itemParms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.SysManage.Organization.Delete(int iOrganiz_ID)";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return iIsSuccess;
        }

        #endregion

        private static SqlParameter[] GetInsertParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_INSERT);
            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter(PARM_PARENT_ID, SqlDbType.Int, 4),
                    new SqlParameter(PARM_ORGANIZ_NAME, SqlDbType.VarChar, 200),
					new SqlParameter(PARM_ORGANIZ_CODE, SqlDbType.VarChar, 100),
                    new SqlParameter(PARM_ORGANIZ_DESC, SqlDbType.VarChar, 600),
                    new SqlParameter(PARM_ORGANIZ_TYPE, SqlDbType.SmallInt,2),
                    new SqlParameter(PARM_ORGANIZ_SORD, SqlDbType.Int, 4),
					new SqlParameter(PARM_CREATOR, SqlDbType.Int, 4)                
                };

                SQLHelper.CacheParameters(SQL_INSERT, parms);
            }

            return parms;
        }

        private static SqlParameter[] GetModifyParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_UPDATE);
            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARM_ORGANIZ_ID,SqlDbType.Int,4),
					new SqlParameter(PARM_PARENT_ID, SqlDbType.Int, 4),
                    new SqlParameter(PARM_ORGANIZ_NAME, SqlDbType.VarChar, 200),
					new SqlParameter(PARM_ORGANIZ_CODE, SqlDbType.VarChar, 100),
                    new SqlParameter(PARM_ORGANIZ_DESC, SqlDbType.VarChar, 600),
                    new SqlParameter(PARM_ORGANIZ_TYPE, SqlDbType.SmallInt,2),
                    new SqlParameter(PARM_ORGANIZ_SORD, SqlDbType.Int, 4)
                };
                SQLHelper.CacheParameters(SQL_UPDATE, parms);
            }

            return parms;
        }

        private void SetInsertParameters(SqlParameter[] parms, OrganizInfo myOrganizInfo)
        {
            parms[0].Value = myOrganizInfo.Parent_Node_ID;
            parms[1].Value = myOrganizInfo.Node_Name;
            parms[2].Value = myOrganizInfo.OrganizCode;
            parms[3].Value = myOrganizInfo.Node_Desc;
            parms[4].Value = myOrganizInfo.OrganizType;
            parms[5].Value = myOrganizInfo.OrganizSord;
            parms[6].Value = myOrganizInfo.Creator;
        }

        private void SetModifyParameters(SqlParameter[] parms, OrganizInfo myOrganizInfo)
        {
            parms[0].Value = myOrganizInfo.Node_ID;
            parms[1].Value = myOrganizInfo.Parent_Node_ID;
            parms[2].Value = myOrganizInfo.Node_Name;
            parms[3].Value = myOrganizInfo.OrganizCode;
            parms[4].Value = myOrganizInfo.Node_Desc;
            parms[5].Value = myOrganizInfo.OrganizType;
            parms[6].Value = myOrganizInfo.OrganizSord;
        }


        #region ITree Members

        public IList getCurrNode(int iNode_ID)
        {
            IList nodeList = new ArrayList();
            try
            {
                SqlParameter parm = new SqlParameter(PARM_ORGANIZ_ID, SqlDbType.Int, 4,"organiz_uid");
                parm.Value = iNode_ID;

                //Execute a query to read the products
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_NODE, parm))
                {
                    while (rdr.Read())
                    {
                        OrganizInfo nodeInfo = new OrganizInfo(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetInt16(5), rdr.GetInt32(6),"","0");
                        //int Item_ID,int Parent_Item_ID, string Item_Name,string Organiz_Code, string Item_Desc, Int16 Organiz_Type, int Organiz_Sord, string Link, string IsLeaf
                        nodeList.Add(nodeInfo);
                    }
                }
            }
            catch (Exception err)
            {
                string sMethod = "emp.SqlServerDAL.SysManage.Organization.getCurrNode(int iNode_ID)";
                EventsLog.WriteLog(err, sMethod);
            }
            return nodeList;
        }

        public IList getChildNodeList(string isdel, int parent_node_id)
        {
            IList nodeList = new ArrayList();

            SqlParameter parm = new SqlParameter(PARM_PARENT_ID, SqlDbType.Int, 4,"Parent_ID");            
            parm.Value = parent_node_id;

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_CHILDLIST, parm))
            {
                while (rdr.Read())
                {
                    TreeInfo nodeInfo = new TreeInfo(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetString(2), rdr.GetString(3));
                    //int Item_ID, int Parent_Item_ID, string Item_Name, string Item_Desc
                    nodeList.Add(nodeInfo);
                }
            }

            return nodeList;
        }

        #endregion
    }
}
